package com.mcc.app.siloReport.service.impl;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.MatchResult;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.mcc.app.utils.MyExcelUtil;
import com.mcc.common.config.ProjectConfig;
import com.mcc.common.exception.CustomException;
import com.mcc.common.utils.uuid.IdUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellCopyPolicy;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.mcc.app.siloReport.mapper.SiloDayReportMapper;
import com.mcc.app.siloReport.domain.SiloDayReport;
import com.mcc.app.siloReport.service.ISiloDayReportService;

import static com.mcc.common.utils.file.FileUploadUtils.getAbsoluteFile;

/**
 * 配料日报Service业务层处理
 *
 * @author gxy
 * @date 2025-03-25
 */
@Service
public class SiloDayReportServiceImpl implements ISiloDayReportService {

    @Autowired
    private SiloDayReportMapper siloDayReportMapper;

    /**
     * 查询配料日报
     *
     * @param id 配料日报ID
     * @return 配料日报
     */
    @Override
    public SiloDayReport selectSiloDayReportById(Long id) {
        return siloDayReportMapper.selectById(id);
    }

    /**
     * 查询配料日报列表
     *
     * @param siloDayReport 配料日报
     * @return 配料日报
     */
    @Override
    public List<SiloDayReport> selectSiloDayReportList(SiloDayReport siloDayReport) {
        QueryWrapper queryWrapper = new QueryWrapper();
        if (ObjectUtil.isNotEmpty(siloDayReport)) {
            queryWrapper.eq(ObjectUtil.isNotEmpty(siloDayReport.getDt()), "dt", siloDayReport.getDt());
        }
        return siloDayReportMapper.selectList(queryWrapper);
    }

    /**
     * 新增配料日报
     *
     * @param siloDayReport 配料日报
     * @return 结果
     */
    @Override
    public int insertSiloDayReport(SiloDayReport siloDayReport) {
        return siloDayReportMapper.insert(siloDayReport);
    }

    /**
     * 修改配料日报
     *
     * @param siloDayReport 配料日报
     * @return 结果
     */
    @Override
    public int updateSiloDayReport(SiloDayReport siloDayReport) {
        return siloDayReportMapper.updateById(siloDayReport);
    }

    /**
     * 删除配料日报
     *
     * @param id 配料日报ID
     * @return 结果
     */
    @Override
    public int deleteSiloDayReportById(Long id) {
        return siloDayReportMapper.deleteById(id);
    }

    /**
     * 批量删除配料日报
     *
     * @param ids 配料日报ID
     * @return 结果
     */
    @Override
    public int deleteSiloDayReportByIds(Long[] ids) {
        if (ids.length == 0) return 0;
        return siloDayReportMapper.deleteBatchIds(ListUtil.toList(ids));
    }

    public String exportSiloDayReport(SiloDayReport siloDayReport) {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.ge(ObjectUtil.isNotEmpty(siloDayReport.getBeginTime()), "dt", siloDayReport.getBeginTime());
        queryWrapper.le(ObjectUtil.isNotEmpty(siloDayReport.getEndTime()), "dt", siloDayReport.getEndTime());
        queryWrapper.orderByAsc("dt");
        List<SiloDayReport> siloDayReportList = siloDayReportMapper.selectList(queryWrapper);
        InputStream template = getClass().getClassLoader().getResourceAsStream("template/配料日报表模板.xls");
        ExcelReader reader = ExcelUtil.getReader(template);
        ExcelWriter exportWriter = reader.getWriter();

        replicateTemplate(exportWriter.getWorkbook().getSheetAt(0), siloDayReportList.size() - 1);

        exportWriter.disableDefaultStyle();
        String filename = IdUtils.fastSimpleUUID() + "_配料生产统计表" +
                DateUtil.format(siloDayReport.getBeginTime(), "yyyyMMdd") + "-" +
                DateUtil.format(siloDayReport.getEndTime(), "yyyyMMdd") + ".xls";

        for (int i = 0; i < siloDayReportList.size(); i++) {
            SiloDayReport siloDayReport1 = siloDayReportList.get(i);
            exportWriter = writeSiloReportSheet(i, exportWriter, siloDayReport1);
        }
        exportWriter.getWorkbook().setForceFormulaRecalculation(true);

        try {
            OutputStream out = new FileOutputStream(getAbsoluteFile(ProjectConfig.getDownloadPath(), filename));
            exportWriter.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
            throw new CustomException("导出失败");
        } finally {
            exportWriter.close();
        }
        return filename;
    }

    public ExcelWriter writeSiloReportSheet(Integer index, ExcelWriter exportWriter, SiloDayReport siloDayReport) {
        int colIndex = 8 * index + 1;
        exportWriter.setSheet(0);
        MyExcelUtil.writeCellValue(exportWriter, "A" + colIndex, DateUtil.format(siloDayReport.getDt(), "yyyyMMdd") + "配料生产统计");
        MyExcelUtil.writeCellValue(exportWriter, "B" + (colIndex + 2), siloDayReport.getSt2Ingredient1Name());
        MyExcelUtil.writeCellValue(exportWriter, "C" + (colIndex + 2), siloDayReport.getSt2Ingredient2Name());
        MyExcelUtil.writeCellValue(exportWriter, "D" + (colIndex + 2), siloDayReport.getSt2Ingredient3Name());
        MyExcelUtil.writeCellValue(exportWriter, "E" + (colIndex + 2), siloDayReport.getSt2Ingredient4Name());
        MyExcelUtil.writeCellValue(exportWriter, "F" + (colIndex + 2), siloDayReport.getSt2Ingredient5Name());
        MyExcelUtil.writeCellValue(exportWriter, "G" + (colIndex + 2), siloDayReport.getSt2Ingredient6Name());
        MyExcelUtil.writeCellValue(exportWriter, "H" + (colIndex + 2), siloDayReport.getSt2Ingredient7Name());
        MyExcelUtil.writeCellValue(exportWriter, "I" + (colIndex + 2), siloDayReport.getSt2Ingredient8Name());
        MyExcelUtil.writeCellValue(exportWriter, "J" + (colIndex + 2), siloDayReport.getSt2Ingredient9Name());
        MyExcelUtil.writeCellValue(exportWriter, "K" + (colIndex + 2), siloDayReport.getSt2Ingredient10Name());
        MyExcelUtil.writeCellValue(exportWriter, "L" + (colIndex + 2), siloDayReport.getSt2Ingredient11Name());
        MyExcelUtil.writeCellValue(exportWriter, "M" + (colIndex + 2), siloDayReport.getSt2Ingredient12Name());
        MyExcelUtil.writeCellValue(exportWriter, "N" + (colIndex + 2), siloDayReport.getSt2Ingredient13Name());
        MyExcelUtil.writeCellValue(exportWriter, "O" + (colIndex + 2), siloDayReport.getSt2Ingredient14Name());
        MyExcelUtil.writeCellValue(exportWriter, "P" + (colIndex + 2), siloDayReport.getSt2Ingredient15Name());
        MyExcelUtil.writeCellValue(exportWriter, "Q" + (colIndex + 2), siloDayReport.getSt2Ingredient16Name());
        MyExcelUtil.writeCellValue(exportWriter, "R" + (colIndex + 2), siloDayReport.getSt2Ingredient17Name());
        MyExcelUtil.writeCellValue(exportWriter, "S" + (colIndex + 2), siloDayReport.getSt2Ingredient18Name());
        MyExcelUtil.writeCellValue(exportWriter, "T" + (colIndex + 2), siloDayReport.getSt2Ingredient19Name());
        MyExcelUtil.writeCellValue(exportWriter, "U" + (colIndex + 2), siloDayReport.getSt2Ingredient20Name());
        MyExcelUtil.writeCellValue(exportWriter, "V" + (colIndex + 2), siloDayReport.getSt2Ingredient21Name());
        MyExcelUtil.writeCellValue(exportWriter, "W" + (colIndex + 2), siloDayReport.getSt2Ingredient22Name());
        MyExcelUtil.writeCellValue(exportWriter, "X" + (colIndex + 2), siloDayReport.getSt2Ingredient23Name());

        MyExcelUtil.writeCellValue(exportWriter, "B" + (colIndex + 3), siloDayReport.getSt2Night1Total());
        MyExcelUtil.writeCellValue(exportWriter, "C" + (colIndex + 3), siloDayReport.getSt2Night2Total());
        MyExcelUtil.writeCellValue(exportWriter, "D" + (colIndex + 3), siloDayReport.getSt2Night3Total());
        MyExcelUtil.writeCellValue(exportWriter, "E" + (colIndex + 3), siloDayReport.getSt2Night4Total());
        MyExcelUtil.writeCellValue(exportWriter, "F" + (colIndex + 3), siloDayReport.getSt2Night5Total());
        MyExcelUtil.writeCellValue(exportWriter, "G" + (colIndex + 3), siloDayReport.getSt2Night6Total());
        MyExcelUtil.writeCellValue(exportWriter, "H" + (colIndex + 3), siloDayReport.getSt2Night7Total());
        MyExcelUtil.writeCellValue(exportWriter, "I" + (colIndex + 3), siloDayReport.getSt2Night8Total());
        MyExcelUtil.writeCellValue(exportWriter, "J" + (colIndex + 3), siloDayReport.getSt2Night9Total());
        MyExcelUtil.writeCellValue(exportWriter, "K" + (colIndex + 3), siloDayReport.getSt2Night10Total());
        MyExcelUtil.writeCellValue(exportWriter, "L" + (colIndex + 3), siloDayReport.getSt2Night11Total());
        MyExcelUtil.writeCellValue(exportWriter, "M" + (colIndex + 3), siloDayReport.getSt2Night12Total());
        MyExcelUtil.writeCellValue(exportWriter, "N" + (colIndex + 3), siloDayReport.getSt2Night13Total());
        MyExcelUtil.writeCellValue(exportWriter, "O" + (colIndex + 3), siloDayReport.getSt2Night14Total());
        MyExcelUtil.writeCellValue(exportWriter, "P" + (colIndex + 3), siloDayReport.getSt2Night15Total());
        MyExcelUtil.writeCellValue(exportWriter, "Q" + (colIndex + 3), siloDayReport.getSt2Night16Total());
        MyExcelUtil.writeCellValue(exportWriter, "R" + (colIndex + 3), siloDayReport.getSt2Night17Total());
        MyExcelUtil.writeCellValue(exportWriter, "S" + (colIndex + 3), siloDayReport.getSt2Night18Total());
        MyExcelUtil.writeCellValue(exportWriter, "T" + (colIndex + 3), siloDayReport.getSt2Night19Total());
        MyExcelUtil.writeCellValue(exportWriter, "U" + (colIndex + 3), siloDayReport.getSt2Night20Total());
        MyExcelUtil.writeCellValue(exportWriter, "V" + (colIndex + 3), siloDayReport.getSt2Night21Total());
        MyExcelUtil.writeCellValue(exportWriter, "W" + (colIndex + 3), siloDayReport.getSt2Night22Total());
        MyExcelUtil.writeCellValue(exportWriter, "X" + (colIndex + 3), siloDayReport.getSt2Night23Total());
        MyExcelUtil.writeCellValue(exportWriter, "Y" + (colIndex + 3),
                "SUM(B" + (colIndex + 3) + ":X" + (colIndex + 3) + ")", true);

        MyExcelUtil.writeCellValue(exportWriter, "B" + (colIndex + 4), siloDayReport.getSt2Day1Total());
        MyExcelUtil.writeCellValue(exportWriter, "C" + (colIndex + 4), siloDayReport.getSt2Day2Total());
        MyExcelUtil.writeCellValue(exportWriter, "D" + (colIndex + 4), siloDayReport.getSt2Day3Total());
        MyExcelUtil.writeCellValue(exportWriter, "E" + (colIndex + 4), siloDayReport.getSt2Day4Total());
        MyExcelUtil.writeCellValue(exportWriter, "F" + (colIndex + 4), siloDayReport.getSt2Day5Total());
        MyExcelUtil.writeCellValue(exportWriter, "G" + (colIndex + 4), siloDayReport.getSt2Day6Total());
        MyExcelUtil.writeCellValue(exportWriter, "H" + (colIndex + 4), siloDayReport.getSt2Day7Total());
        MyExcelUtil.writeCellValue(exportWriter, "I" + (colIndex + 4), siloDayReport.getSt2Day8Total());
        MyExcelUtil.writeCellValue(exportWriter, "J" + (colIndex + 4), siloDayReport.getSt2Day9Total());
        MyExcelUtil.writeCellValue(exportWriter, "K" + (colIndex + 4), siloDayReport.getSt2Day10Total());
        MyExcelUtil.writeCellValue(exportWriter, "L" + (colIndex + 4), siloDayReport.getSt2Day11Total());
        MyExcelUtil.writeCellValue(exportWriter, "M" + (colIndex + 4), siloDayReport.getSt2Day12Total());
        MyExcelUtil.writeCellValue(exportWriter, "N" + (colIndex + 4), siloDayReport.getSt2Day13Total());
        MyExcelUtil.writeCellValue(exportWriter, "O" + (colIndex + 4), siloDayReport.getSt2Day14Total());
        MyExcelUtil.writeCellValue(exportWriter, "P" + (colIndex + 4), siloDayReport.getSt2Day15Total());
        MyExcelUtil.writeCellValue(exportWriter, "Q" + (colIndex + 4), siloDayReport.getSt2Day16Total());
        MyExcelUtil.writeCellValue(exportWriter, "R" + (colIndex + 4), siloDayReport.getSt2Day17Total());
        MyExcelUtil.writeCellValue(exportWriter, "S" + (colIndex + 4), siloDayReport.getSt2Day18Total());
        MyExcelUtil.writeCellValue(exportWriter, "T" + (colIndex + 4), siloDayReport.getSt2Day19Total());
        MyExcelUtil.writeCellValue(exportWriter, "U" + (colIndex + 4), siloDayReport.getSt2Day20Total());
        MyExcelUtil.writeCellValue(exportWriter, "V" + (colIndex + 4), siloDayReport.getSt2Day21Total());
        MyExcelUtil.writeCellValue(exportWriter, "W" + (colIndex + 4), siloDayReport.getSt2Day22Total());
        MyExcelUtil.writeCellValue(exportWriter, "X" + (colIndex + 4), siloDayReport.getSt2Day23Total());
        MyExcelUtil.writeCellValue(exportWriter, "Y" + (colIndex + 4),
                "SUM(B" + (colIndex + 4) + ":X" + (colIndex + 4) + ")", true);

        MyExcelUtil.writeCellValue(exportWriter, "B" + (colIndex + 5), siloDayReport.getSt2Mid1Total());
        MyExcelUtil.writeCellValue(exportWriter, "C" + (colIndex + 5), siloDayReport.getSt2Mid2Total());
        MyExcelUtil.writeCellValue(exportWriter, "D" + (colIndex + 5), siloDayReport.getSt2Mid3Total());
        MyExcelUtil.writeCellValue(exportWriter, "E" + (colIndex + 5), siloDayReport.getSt2Mid4Total());
        MyExcelUtil.writeCellValue(exportWriter, "F" + (colIndex + 5), siloDayReport.getSt2Mid5Total());
        MyExcelUtil.writeCellValue(exportWriter, "G" + (colIndex + 5), siloDayReport.getSt2Mid6Total());
        MyExcelUtil.writeCellValue(exportWriter, "H" + (colIndex + 5), siloDayReport.getSt2Mid7Total());
        MyExcelUtil.writeCellValue(exportWriter, "I" + (colIndex + 5), siloDayReport.getSt2Mid8Total());
        MyExcelUtil.writeCellValue(exportWriter, "J" + (colIndex + 5), siloDayReport.getSt2Mid9Total());
        MyExcelUtil.writeCellValue(exportWriter, "K" + (colIndex + 5), siloDayReport.getSt2Mid10Total());
        MyExcelUtil.writeCellValue(exportWriter, "L" + (colIndex + 5), siloDayReport.getSt2Mid11Total());
        MyExcelUtil.writeCellValue(exportWriter, "M" + (colIndex + 5), siloDayReport.getSt2Mid12Total());
        MyExcelUtil.writeCellValue(exportWriter, "N" + (colIndex + 5), siloDayReport.getSt2Mid13Total());
        MyExcelUtil.writeCellValue(exportWriter, "O" + (colIndex + 5), siloDayReport.getSt2Mid14Total());
        MyExcelUtil.writeCellValue(exportWriter, "P" + (colIndex + 5), siloDayReport.getSt2Mid15Total());
        MyExcelUtil.writeCellValue(exportWriter, "Q" + (colIndex + 5), siloDayReport.getSt2Mid16Total());
        MyExcelUtil.writeCellValue(exportWriter, "R" + (colIndex + 5), siloDayReport.getSt2Mid17Total());
        MyExcelUtil.writeCellValue(exportWriter, "S" + (colIndex + 5), siloDayReport.getSt2Mid18Total());
        MyExcelUtil.writeCellValue(exportWriter, "T" + (colIndex + 5), siloDayReport.getSt2Mid19Total());
        MyExcelUtil.writeCellValue(exportWriter, "U" + (colIndex + 5), siloDayReport.getSt2Mid20Total());
        MyExcelUtil.writeCellValue(exportWriter, "V" + (colIndex + 5), siloDayReport.getSt2Mid21Total());
        MyExcelUtil.writeCellValue(exportWriter, "W" + (colIndex + 5), siloDayReport.getSt2Mid22Total());
        MyExcelUtil.writeCellValue(exportWriter, "X" + (colIndex + 5), siloDayReport.getSt2Mid23Total());
        MyExcelUtil.writeCellValue(exportWriter, "Y" + (colIndex + 5),
                "SUM(B" + (colIndex + 5) + ":X" + (colIndex + 5) + ")", true);

        MyExcelUtil.writeCellValue(exportWriter, "B" + (colIndex + 6), siloDayReport.getSt2Previous1Daily());
        MyExcelUtil.writeCellValue(exportWriter, "C" + (colIndex + 6), siloDayReport.getSt2Previous2Daily());
        MyExcelUtil.writeCellValue(exportWriter, "D" + (colIndex + 6), siloDayReport.getSt2Previous3Daily());
        MyExcelUtil.writeCellValue(exportWriter, "E" + (colIndex + 6), siloDayReport.getSt2Previous4Daily());
        MyExcelUtil.writeCellValue(exportWriter, "F" + (colIndex + 6), siloDayReport.getSt2Previous5Daily());
        MyExcelUtil.writeCellValue(exportWriter, "G" + (colIndex + 6), siloDayReport.getSt2Previous6Daily());
        MyExcelUtil.writeCellValue(exportWriter, "H" + (colIndex + 6), siloDayReport.getSt2Previous7Daily());
        MyExcelUtil.writeCellValue(exportWriter, "I" + (colIndex + 6), siloDayReport.getSt2Previous8Daily());
        MyExcelUtil.writeCellValue(exportWriter, "J" + (colIndex + 6), siloDayReport.getSt2Previous9Daily());
        MyExcelUtil.writeCellValue(exportWriter, "K" + (colIndex + 6), siloDayReport.getSt2Previous10Daily());
        MyExcelUtil.writeCellValue(exportWriter, "L" + (colIndex + 6), siloDayReport.getSt2Previous11Daily());
        MyExcelUtil.writeCellValue(exportWriter, "M" + (colIndex + 6), siloDayReport.getSt2Previous12Daily());
        MyExcelUtil.writeCellValue(exportWriter, "N" + (colIndex + 6), siloDayReport.getSt2Previous13Daily());
        MyExcelUtil.writeCellValue(exportWriter, "O" + (colIndex + 6), siloDayReport.getSt2Previous14Daily());
        MyExcelUtil.writeCellValue(exportWriter, "P" + (colIndex + 6), siloDayReport.getSt2Previous15Daily());
        MyExcelUtil.writeCellValue(exportWriter, "Q" + (colIndex + 6), siloDayReport.getSt2Previous16Daily());
        MyExcelUtil.writeCellValue(exportWriter, "R" + (colIndex + 6), siloDayReport.getSt2Previous17Daily());
        MyExcelUtil.writeCellValue(exportWriter, "S" + (colIndex + 6), siloDayReport.getSt2Previous18Daily());
        MyExcelUtil.writeCellValue(exportWriter, "T" + (colIndex + 6), siloDayReport.getSt2Previous19Daily());
        MyExcelUtil.writeCellValue(exportWriter, "U" + (colIndex + 6), siloDayReport.getSt2Previous20Daily());
        MyExcelUtil.writeCellValue(exportWriter, "V" + (colIndex + 6), siloDayReport.getSt2Previous21Daily());
        MyExcelUtil.writeCellValue(exportWriter, "W" + (colIndex + 6), siloDayReport.getSt2Previous22Daily());
        MyExcelUtil.writeCellValue(exportWriter, "X" + (colIndex + 6), siloDayReport.getSt2Previous23Daily());
        MyExcelUtil.writeCellValue(exportWriter, "Y" + (colIndex + 6),
                "SUM(B" + (colIndex + 6) + ":X" + (colIndex + 6) + ")", true);

        return exportWriter;
    }

    public static void replicateTemplate(Sheet sheet, int num) {
        final int TEMPLATE_START_ROW = 0;  // 模板起始行（0-based）
        final int TEMPLATE_END_ROW = 7;    // 模板结束行（0-based）
        final int ROWS_PER_TEMPLATE = 8;   // 每个模板占9行

        for (int i = 1; i <= num; i++) {
            int targetStartRow = i * ROWS_PER_TEMPLATE;
            copyTemplateBlock(sheet, TEMPLATE_START_ROW, TEMPLATE_END_ROW, targetStartRow);
        }
    }

    private static void copyTemplateBlock(Sheet sheet, int srcStart, int srcEnd, int targetStart) {
        // 复制行内容
        for (int rowNum = srcStart; rowNum <= srcEnd; rowNum++) {
            Row sourceRow = sheet.getRow(rowNum);
            if (sourceRow == null) continue;

            Row newRow = sheet.createRow(targetStart + (rowNum - srcStart));
            copyRowStyleAndContent(sourceRow, newRow);
        }

        // 复制合并区域
        copyMergedRegions(sheet, srcStart, srcEnd, targetStart);
    }

    private static void copyRowStyleAndContent(Row sourceRow, Row newRow) {
        newRow.setHeight(sourceRow.getHeight());

        for (int cellNum = 0; cellNum < sourceRow.getLastCellNum(); cellNum++) {
            Cell sourceCell = sourceRow.getCell(cellNum);
            if (sourceCell == null) continue;

            Cell newCell = newRow.createCell(cellNum);
            copyCellStyleAndValue(sourceCell, newCell);
        }
    }

    private static void copyCellStyleAndValue(Cell sourceCell, Cell newCell) {
        // 复制单元格样式
        newCell.setCellStyle(sourceCell.getCellStyle());

        // 复制单元格值
        switch (sourceCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                handleFormulaCopy(sourceCell, newCell);
                break;
            case Cell.CELL_TYPE_BLANK:
                newCell.setCellType(Cell.CELL_TYPE_BLANK);
                break;
            default:
                newCell.setCellValue("");
        }
    }

    private static void handleFormulaCopy(Cell sourceCell, Cell newCell) {
        // 处理公式的相对位置调整
        String originalFormula = sourceCell.getCellFormula();
        int rowShift = newCell.getRowIndex() - sourceCell.getRowIndex();
        String adjustedFormula = adjustFormulaReferences(originalFormula, rowShift);
        newCell.setCellFormula(adjustedFormula);
    }

    private static String adjustFormulaReferences(String formula, final int rowShift) {
        Pattern pattern = Pattern.compile("([A-Z]+)(\\d+)");
        Matcher matcher = pattern.matcher(formula);

        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String col = matcher.group(1);
            int row = Integer.parseInt(matcher.group(2));
            String replacement = col + (row + rowShift);

            matcher.appendReplacement(sb, Matcher.quoteReplacement(replacement));
        }
        matcher.appendTail(sb);

        return sb.toString();
    }


    private static void copyMergedRegions(Sheet sheet, int srcStart, int srcEnd, int targetStart) {
        int rowShift = targetStart - srcStart;

        sheet.getMergedRegions().stream()
                .filter(region -> region.getFirstRow() >= srcStart && region.getLastRow() <= srcEnd)
                .forEach(region -> {
                    CellRangeAddress newRegion = new CellRangeAddress(
                            region.getFirstRow() + rowShift,
                            region.getLastRow() + rowShift,
                            region.getFirstColumn(),
                            region.getLastColumn()
                    );
                    sheet.addMergedRegion(newRegion);
                });
    }
}
